package org.cncitrus.data.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;

public class ChinaTadeConn {
	private String TABLE_NAME = "orange_z_jinchuliang";
	private Statement stmt;

	public ChinaTadeConn() throws SQLException {
		stmt = OrangeConn.getInstance().getStatement();
	}
	
	public String load(String[] year, String[] code) throws Exception {
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE "+Params.ChinaTrade.year+" IN "
				+ ParamsManager.Array2String(year) + " AND "+Params.ChinaTrade.code+" IN "
				+ ParamsManager.Array2String(code);
		ResultSet rs = stmt.executeQuery(sql);
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		rs.last();
		sb.append("<resp count=\"" + rs.getRow() + "\"> ");
		rs.beforeFirst();
		while (rs.next()) {
			sb.append("<item year=\"" + rs.getString(Params.ChinaTrade.year).trim()
					+ "\" code=\"" + rs.getString(Params.ChinaTrade.code).trim() + "\""
					+ " i_total=\"" + rs.getString(Params.ChinaTrade.i_total).trim() + "\""
					+ " i_money=\"" + rs.getString(Params.ChinaTrade.i_money).trim() + "\""
					+ " e_total=\"" + rs.getString(Params.ChinaTrade.e_total).trim() + "\"" 
					+ " e_money=\"" + rs.getString(Params.ChinaTrade.e_money).trim() + "\" />");
		}
		sb.append("</resp>");
		return sb.toString();
	}
	
	public String insert(String[] year,String[] code,String[] i_total,String[] i_money,String[] e_total,String[] e_money) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.ChinaTrade.year+","+Params.ChinaTrade.code+","+Params.ChinaTrade.i_total+","+Params.ChinaTrade.i_money+","+Params.ChinaTrade.e_total+","+Params.ChinaTrade.e_money+") VALUES ");		
		
		String content = ParamsManager.getInsertString(year,code,i_total,i_money,e_total,e_money);
		sb.append(content);
		stmt.execute(sb.toString());
		sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<resp rc=\"0\"/>");		
		return sb.toString();
	}
	
}
